using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Data.Common;

namespace Pitoshka.DB.MSSql
{
	public class MSSqlEngine : SqlEngine
	{
		private SqlConnection sqlConnection;
		protected override DbConnection Connection
		{
			get
			{
				return sqlConnection;
			}
		}


		public MSSqlEngine() : this(PreferredConnString) { }
		public MSSqlEngine(string connectionString) : base(connectionString)
        {
            this.sqlConnection = new SqlConnection(connectionString);
        }

		public override DataTable ExecuteSelect(string sql, CommandType commandType, DB.DBParameter[] parameters)
		{
			OpenConnection();
			SqlCommand sqlCommand = this.CreateDbCommand(sql, commandType, parameters);

			SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
			DataTable dataTable = new DataTable();
			dataTable.BeginLoadData();
			dataAdapter.Fill(dataTable);
			dataTable.EndLoadData();
			dataAdapter.Dispose();
			sqlCommand.Dispose();
			CloseConnection();
			return dataTable;

		}
		public override DataSet ExecuteDataSet(string sql, CommandType commandType, DB.DBParameter[] parameters)
		{
			OpenConnection();
			SqlCommand sqlCommand = this.CreateDbCommand(sql, commandType, parameters);

			SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
			DataSet dataSet = new DataSet();
			dataAdapter.Fill(dataSet);
			dataAdapter.Dispose();
			sqlCommand.Dispose();
			CloseConnection();
			return dataSet;

		}
		public override XmlDocument ExecuteXML(string sql, CommandType commandType, DBParameter[] parameters)
		{
			OpenConnection();
			SqlCommand sqlCommand = this.CreateDbCommand(sql, commandType, parameters);
			
			XmlDocument retval = new XmlDocument();
			XmlReader rdr = sqlCommand.ExecuteXmlReader();
			retval.Load(rdr);
			rdr.Close();
			sqlCommand.Dispose();
			CloseConnection();
			return retval;

		}
		public override XmlReader ExecuteXMLreader(string sql, CommandType commandType, DBParameter[] parameters)
		{
			throw new Exception("The method or operation is not implemented.");
		}
		public override int ExecuteNonQuery(string sql, CommandType commandType, DBParameter[] parameters)
		{
			OpenConnection();
			SqlCommand sqlCommand = this.CreateDbCommand(sql, commandType, parameters);

			int retval = sqlCommand.ExecuteNonQuery();
			sqlCommand.Dispose();
			CloseConnection();
			return retval;

		}
		public override object ExecuteScalar(string sql, CommandType commandType, DBParameter[] parameters)
		{
			OpenConnection();
			SqlCommand sqlCommand = this.CreateDbCommand(sql, commandType, parameters);
			object retval = sqlCommand.ExecuteScalar();
			sqlCommand.Dispose();
			CloseConnection();
			return retval;

		}
		public override DbDataReader ExecuteReader(string sql, CommandType commandType, DBParameter[] parameters)
		{
			throw new Exception("The method or operation is not implemented.");
		}

		public new SqlCommand CreateDbCommand(string sql, CommandType commandType, DBParameter[] parameters)
		{
			SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
			sqlCommand.CommandType = commandType;

			foreach (DBParameter parameter in parameters)
			{
				sqlCommand.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
			}
			
			return sqlCommand;

		}
	}
}
